﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

namespace BookStore.Models
{
    public class BookModel
    {
        private string connectionString;
        private SqlConnection connect;

        public BookModel()
        {
            connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
            connect = new SqlConnection(connectionString);
        }

        public string[] getBookbyID(int ID)
        {
            string[] result = new string[15];
            try
            {
                connect.Open();
                string selectSQL = "SELECT * FROM Book WHERE status != 0 AND book_id = " + ID.ToString();
                SqlCommand cmd = new SqlCommand(selectSQL, connect);

                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    result[0] = reader["book_code"].ToString();
                    result[1] = reader["book_name"].ToString();
                    result[2] = reader["author_name"].ToString();
                    result[3] = (int)reader["quantity"] != 0 ? reader["price_buy"].ToString()+ " VNĐ" : "Hết hàng !";
                    result[4] = (int)reader["quantity_rent"] != 0 ? reader["price_rent"].ToString() + " VNĐ" : "Hết hàng !";
                    result[5] = reader["quantity"].ToString();
                    result[6] = reader["book_id"].ToString();
                    result[7] = reader["image_url"].ToString() != "" ? reader["image_url"].ToString() : "/content/images/no_image.jpg";
                    result[8] = new DateTime(1970, 1, 1).AddSeconds(Convert.ToInt32(reader["updated_date"].ToString())).ToString("dd-MM-yyyy");
                    result[9] = reader["short_description"].ToString() != "" ? reader["short_description"].ToString() : "Đang Cập Nhật !";
                    result[10] = reader["long_description"].ToString() != "" ? reader["long_description"].ToString() : "Đang Cập Nhật !";
                    result[11] = reader["category_id"].ToString();
                    result[12] = reader["quantity_rent"].ToString();
                    result[13] = reader["price_buy"].ToString();
                    result[14] = reader["price_rent"].ToString();
                }
                reader.Close();
            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                connect.Close();
            }

            return result;
        }


        public string[][] getBookRelativeByID(int cateID,int ID)
        {
            string[][] result = new string[6][];
            try
            {
                connect.Open();
                string selectSQL = "SELECT TOP 6 * FROM Book WHERE status != 0 AND category_id = " + cateID.ToString() + " AND book_id !=" + ID.ToString() + " ORDER BY NEWID()";
                SqlCommand cmd = new SqlCommand(selectSQL, connect);

                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                int i = 0;
                while (reader.Read())
                {
                    result[i] = new string[10];
                    result[i][0] = reader["book_code"].ToString();
                    result[i][1] = reader["book_name"].ToString();
                    result[i][2] = reader["author_name"].ToString();
                    result[i][3] = (int)reader["quantity"] != 0 ? reader["price_buy"].ToString() + " VNĐ" : "Hết hàng !";
                    result[i][4] = (int)reader["quantity_rent"] != 0 ? reader["price_rent"].ToString() + " VNĐ" : "Hết hàng !";
                    result[i][5] = reader["quantity"].ToString();
                    result[i][6] = reader["book_id"].ToString();
                    result[i][7] = reader["image_url"].ToString() != "" ? reader["image_url"].ToString() : "/content/images/no_image.jpg";
                    result[i][8] = new DateTime(1970, 1, 1).AddSeconds(Convert.ToInt32(reader["updated_date"].ToString())).ToString("dd-MM-yyyy");
                    result[i][9] = reader["quantity_rent"].ToString();
                    i++;
                }
                reader.Close();
            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                connect.Close();
            }

            return result;
        }

        public string[][] getBookByCateID(int cateID,int page,int offset)
        {
            string[][] result = new string[12][];
            try
            {
                connect.Open();
                string selectSQL = "WITH LIMIT AS(SELECT *,ROW_NUMBER() OVER(ORDER BY updated_date DESC) AS rowNum FROM Book WHERE status != 0 AND category_id = " + cateID.ToString() + ")";
                selectSQL += "SELECT * FROM LIMIT WHERE rowNum BETWEEN " + ((page - 1) * offset + 1)  + " AND " + (page * offset);
                SqlCommand cmd = new SqlCommand(selectSQL, connect);

                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                int i = 0;
                while (reader.Read())
                {
                    result[i] = new string[10];
                    result[i][0] = reader["book_code"].ToString();
                    result[i][1] = reader["book_name"].ToString();
                    result[i][2] = reader["author_name"].ToString();
                    result[i][3] = (int)reader["quantity"] != 0 ? reader["price_buy"].ToString() + " VNĐ" : "Hết hàng !";
                    result[i][4] = (int)reader["quantity_rent"] != 0 ? reader["price_rent"].ToString() + " VNĐ" : "Hết hàng !";
                    result[i][5] = reader["quantity"].ToString();
                    result[i][6] = reader["book_id"].ToString();
                    result[i][7] = reader["image_url"].ToString() != "" ? reader["image_url"].ToString() : "/content/images/no_image.jpg";
                    result[i][8] = new DateTime(1970, 1, 1).AddSeconds(Convert.ToInt32(reader["updated_date"].ToString())).ToString("dd-MM-yyyy");
                    result[i][9] = reader["quantity_rent"].ToString();
                    i++;
                }
                reader.Close();
            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                connect.Close();
            }

            return result;
        }

        public int countBookbyCateID(int CateID)
        {
            int result = 0;
            try
            {
                connect.Open();
                string selectSQL = "SELECT count(*) as total_book FROM Book WHERE status != 0 AND category_id = " + CateID.ToString();
                SqlCommand cmd = new SqlCommand(selectSQL, connect);

                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                if (reader.Read())
                    result = Convert.ToInt32(reader["total_book"]);
                
                reader.Close();
            }
            catch (Exception err)
            {
                return 0;
            }
            finally
            {
                connect.Close();
            }

            return result;
        }

        public string[][] getBookbySearch(string title, string content, int option, int cate_id, int price_from, int price_to, int price_rent_from, int price_rent_to, int page, int offset)
        {
            string[][] result = new string[9][];
            try
            {
                connect.Open();
                string where = "WHERE status != 0";
                if (cate_id != -1)
                    where += " AND category_id = " + cate_id;
                if (price_from != 0)
                    where += " AND price_buy >= " + price_from;
                if (price_to != 0)
                    where += " AND price_buy <= " + price_to;
                if (price_rent_from != 0)
                    where += " AND price_rent >= " + price_rent_from;
                if (price_rent_to != 0)
                    where += " AND price_rent <= " + price_rent_to;
                if (title != "")
                {
                    title = title.Replace(" ", "%");
                    title = title.Replace("'", "''");
                    where += " AND (book_name LIKE N'%" + title + "%' OR book_code LIKE N'%" + title + "%' OR author_name LIKE N'%" + title + "%')";
                }
                if (content != "")
                {
                    content = content.Replace("'", "''");
                    content = content.Replace(" ", "%");
                    if (option == 1)
                        where += " AND (short_description LIKE N'%" + content + "%' OR long_description LIKE N'%" + content + "%')";
                    if (option == 2)
                        where += " AND author_name LIKE N'%" + content + "%'";
                }

                string selectSQL = "WITH LIMIT AS(SELECT *,ROW_NUMBER() OVER(ORDER BY updated_date DESC) AS rowNum FROM Book " + where + ") ";
                selectSQL += "SELECT * FROM LIMIT WHERE rowNum BETWEEN " + ((page - 1) * offset + 1) + " AND " + (page * offset);
                SqlCommand cmd = new SqlCommand(selectSQL, connect);

                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                int i = 0;
                while (reader.Read())
                {
                    result[i] = new string[10];
                    result[i][0] = reader["book_code"].ToString();
                    result[i][1] = reader["book_name"].ToString();
                    result[i][2] = reader["author_name"].ToString();
                    result[i][3] = (int)reader["quantity"] != 0 ? reader["price_buy"].ToString() + " VNĐ" : "Hết hàng !";
                    result[i][4] = (int)reader["quantity_rent"] != 0 ? reader["price_rent"].ToString() + " VNĐ" : "Hết hàng !";
                    result[i][5] = reader["quantity"].ToString();
                    result[i][6] = reader["book_id"].ToString();
                    result[i][7] = reader["image_url"].ToString() != "" ? reader["image_url"].ToString() : "/content/images/no_image.jpg";
                    result[i][8] = new DateTime(1970, 1, 1).AddSeconds(Convert.ToInt32(reader["updated_date"].ToString())).ToString("dd-MM-yyyy");
                    result[i][9] = reader["quantity_rent"].ToString();
                    i++;
                }
                reader.Close();
            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                connect.Close();
            }

            return result;
        }

        public int countBookbySearch(string title, string content, int option, int cate_id, int price_from, int price_to, int price_rent_from, int price_rent_to)
        {
            int result = 0;
            try
            {
                connect.Open();
                string where = "WHERE status != 0";
                if (cate_id != -1)
                    where += " AND category_id = " + cate_id;
                if (price_from != 0)
                    where += " AND price_buy >= " + price_from;
                if (price_to != 0)
                    where += " AND price_buy <= " + price_to;
                if (price_rent_from != 0)
                    where += " AND price_rent >= " + price_rent_from;
                if (price_rent_to != 0)
                    where += " AND price_rent <= " + price_rent_to;
                if (title != "")
                {
                    title = title.Replace("'", "''");
                    title = title.Replace(" ", "%");
                    where += " AND (book_name LIKE N'%" + title + "%' OR book_code LIKE N'%" + title + "%' OR author_name LIKE N'%" + title + "%')";
                } 
                if (content != "")
                {
                    content = content.Replace("'", "''");
                    content = content.Replace(" ", "%");
                    if (option == 1)
                        where += " AND (short_description LIKE N'%" + content + "%' OR long_description LIKE N'%" + content + "%')";
                    if (option == 2)
                        where += " AND author_name LIKE N'%" + content + "%'";
                }

                string selectSQL = "SELECT count(*) as total_book FROM Book " + where;
                SqlCommand cmd = new SqlCommand(selectSQL, connect);

                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                if (reader.Read())
                    result = Convert.ToInt32(reader["total_book"]);

                reader.Close();
            }
            catch (Exception err)
            {
                return 0;
            }
            finally
            {
                connect.Close();
            }

            return result;
        }

        public DataTable getNewBook()
        {
            DataTable result = new DataTable();
            try
            {
                connect.Open();
                string selectSQL = "SELECT Top 9 * FROM Book WHERE status != 0 Order By created_date DESC";
                SqlCommand cmd = new SqlCommand(selectSQL, connect);

                SqlDataReader reader;
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                result.Load(reader);
            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                connect.Close();
            }

            return result;
        }
        public string[] getDetailBookbyID(int ID)
        {
            string[] result = new string[13];
            try
            {
                connect.Open();
                string selectSQL = "SELECT * FROM Book WHERE status != 0 AND book_id = " + ID.ToString();
                SqlCommand cmd = new SqlCommand(selectSQL, connect);

                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    result[0] = reader["book_code"].ToString();
                    result[1] = reader["book_name"].ToString();
                    result[2] = reader["author_name"].ToString();
                    result[3] = reader["price_buy"].ToString() ;
                    result[4] = reader["price_rent"].ToString();
                    result[5] = reader["quantity"].ToString();
                    result[6] = reader["book_id"].ToString();
                    result[7] = reader["image_url"].ToString() != "" ? reader["image_url"].ToString() : "/content/images/no_image.jpg";
                    result[8] = new DateTime(1970, 1, 1).AddSeconds(Convert.ToInt32(reader["updated_date"].ToString())).ToString("dd-MM-yyyy");
                    result[9] = reader["short_description"].ToString() != "" ? reader["short_description"].ToString() : "Đang Cập Nhật !";
                    result[10] = reader["long_description"].ToString() != "" ? reader["long_description"].ToString() : "Đang Cập Nhật !";
                    result[11] = reader["category_id"].ToString();
                    result[12] = reader["quantity_rent"].ToString();
                }
                reader.Close();
            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                connect.Close();
            }

            return result;
        }

        public string[] getBookPricebyID(int ID)
        {
            string[] result = new string[5];
            try
            {
                connect.Open();
                string selectSQL = "SELECT price_buy,price_rent,book_id,quantity,quantity_rent FROM Book WHERE status != 0 AND book_id = " + ID.ToString();
                SqlCommand cmd = new SqlCommand(selectSQL, connect);

                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    result[0] = reader["book_id"].ToString();
                    result[1] = reader["price_buy"].ToString();
                    result[2] = reader["price_rent"].ToString();
                    result[3] = reader["quantity"].ToString();
                    result[4] = reader["quantity_rent"].ToString();
                }
                reader.Close();
            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                connect.Close();
            }

            return result;
        }
        public string[][] getFavoriteBookByUserID(int user_id,int page, int offset)
         {
             string[][] result = new string[100][];
             string[] temp = new string[100];
                try
                {
                   
                   
                    string[] temp_1 = new string[12];
                   
                    connect.Open();                 
                    string selectSQL_1 = "WITH LIMIT AS(SELECT book_id,ROW_NUMBER() OVER(ORDER BY book_id DESC) AS rowNum FROM Favorite_Book WHERE user_id = " + user_id.ToString() + ")";
                    selectSQL_1 += "SELECT * FROM LIMIT WHERE rowNum BETWEEN " + ((page - 1) * offset + 1) + " AND " + (page * offset);
                    SqlCommand cmd_1 = new SqlCommand(selectSQL_1, connect);

                    SqlDataReader reader_1;
                    reader_1 = cmd_1.ExecuteReader();
                    int i = 0;
                    while (reader_1.Read())
                    {
                        temp[i] = reader_1["book_id"].ToString();
                        i++;
                    }
                    reader_1.Close();
                    
                   
                    
                }
                catch (Exception err)
                {
                    return null;
                }
                finally
                {
                    connect.Close();
                }
                int j = 0;
                try
                {
                    foreach (string te in temp)
                    {
                        if (te != null)
                        {
                            result[j] = this.getDetailBookbyID(Convert.ToInt32(te));
                            j++;
                        }
                        else
                            break;
                    }
                }
                catch (Exception err)
                {
                    return null;
                }
                return result;

        }
        public int getCountFavoriteBookByUserID(int user_id)
        {
            int result = 0;
            try
            {
                connect.Open();
               
                string selectSQL = "SELECT count(*) as total_book FROM Favorite_Book where user_id="+user_id;
                SqlCommand cmd = new SqlCommand(selectSQL, connect);

                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                if (reader.Read())
                    result = Convert.ToInt32(reader["total_book"]);

                reader.Close();
            }
            catch (Exception err)
            {
                return 0;
            }
            finally
            {
                connect.Close();
            }

            return result;
        }
        public string[] getBookNameImageByID(int id)
        {
            string[] result = new string[2];
            try
            {
                connect.Open();

                string selectSQL = "SELECT book_name,image_url FROM Book where book_id=" + id;
                SqlCommand cmd = new SqlCommand(selectSQL, connect);

                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    result[0] = reader["book_name"].ToString();
                    result[1] = reader["image_url"].ToString();
                }
                reader.Close();
            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                connect.Close();
            }

            return result;
        }
    }
}
